﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Common.Tools
{
    public enum CONDITION { Less, Equal, Greater };

    class SQLCondition
    {
        public string TableL;
        public string TableR;
        public CONDITION condition;
        public string ColL;
        public string ColR;
    };

    class SQLOrderBy
    {
        public string tableName;
        public string orderBy;
    };

    public class SqlHelper
    {
        char[] chMap = new char[] { 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z' };
        Dictionary<string, List<string>> colsArray;
        List<SQLCondition> condArray = new List<SQLCondition>();
        List<SQLOrderBy> orderArray;

        public void AddRelation(string tableL, string colL, CONDITION condtion, string tableR, string colR)
        {
            SQLCondition cond = new SQLCondition();
            cond.TableL = tableL;
            cond.TableR = tableR;
            if (colsArray == null)
            {
                if (string.IsNullOrEmpty(tableL) == false || string.IsNullOrEmpty(tableR) == false)
                    colsArray = new Dictionary<string, List<string>>();
                if (string.IsNullOrEmpty(tableL) == false)
                    colsArray.Add(tableL, null);
                if (string.IsNullOrEmpty(tableR) == false && tableL != tableR)
                    colsArray.Add(tableR, null);
            }
            else
            {
                if (colsArray.Keys.Contains(tableL) == false)
                {
                    colsArray.Add(tableL, null);
                }
                if (colsArray.Keys.Contains(tableR) == false)
                {
                    colsArray.Add(tableR, null);
                }
            }
            cond.condition = condtion;
            cond.ColL = colL;
            cond.ColR = colR;
            condArray.Add(cond);
        }

        public void AddTableMap(Dictionary<string, List<string>> array)
        {
            if (colsArray == null)
            {
                colsArray = new Dictionary<string, List<string>>();
            }
            foreach (string str in array.Keys)
            {
                if (colsArray.Keys.Contains(str))
                {
                    if (array[str] == null || array[str].Count < 1) continue;
                    foreach (string strCol in array[str])
                    {
                        if (colsArray[str].Contains(strCol) == false)
                            colsArray[str].Add(strCol);
                    }
                }
                else
                {
                    colsArray.Add(str, array[str]);
                }
            }
            colsArray = array;
        }

        public void AddOrderBy(string colName, string tableName, bool bDesc = false)
        {
            if (orderArray == null) orderArray = new List<SQLOrderBy>();
            colName = colName.ToUpper();
            if (bDesc)
            {
                colName += " DESC";
            }
            SQLOrderBy order = new SQLOrderBy();
            order.tableName = tableName;
            order.orderBy = colName;
            orderArray.Add(order);
        }

        public string Product()
        {
            if (colsArray == null) return "";
            if (colsArray.Count > chMap.Count()) return "";
            int pos = 0;
            string strSelect = "";
            string strFrom = "";
            string strWhere = "";
            Dictionary<string, string> tableMap = new Dictionary<string, string>();
            foreach (string st in colsArray.Keys)
            {
                tableMap.Add(st, chMap[pos].ToString());
                pos++;
            }
            foreach (string st in colsArray.Keys)
            {
                if (string.IsNullOrEmpty(st) == false)
                {
                    if (string.IsNullOrEmpty(strFrom) == false) strFrom += ",";
                    strFrom += st;
                    strFrom += " ";
                    strFrom += tableMap[st];
                }
                if (colsArray[st] == null) continue;
                foreach (string col in colsArray[st])
                {
                    if (string.IsNullOrEmpty(strSelect) == false) strSelect += ",";
                    strSelect += tableMap[st];
                    strSelect += ".";
                    strSelect += col;
                }
            }
            foreach (SQLCondition cond in condArray)
            {
                if (tableMap.Keys.Contains(cond.TableL) == false ||
                    tableMap.Keys.Contains(cond.TableR) == false)
                    continue;
                if (string.IsNullOrEmpty(strWhere) == false) strWhere += " AND ";
                if (string.IsNullOrEmpty(cond.TableL) == false)
                {
                    strWhere += tableMap[cond.TableL];
                    strWhere += ".";
                }
                else
                {
                    strWhere += "'";
                }
                strWhere += cond.ColL;
                if (string.IsNullOrEmpty(cond.TableL))
                {
                    strWhere += "'";
                }
                if (cond.condition == CONDITION.Less)
                {
                    strWhere += " < ";
                }
                else if (cond.condition == CONDITION.Equal)
                {
                    strWhere += " = ";
                }
                else if (cond.condition == CONDITION.Greater)
                {
                    strWhere += " > ";
                }
                if (string.IsNullOrEmpty(cond.TableR) == false)
                {
                    strWhere += tableMap[cond.TableR];
                    strWhere += ".";
                }
                else
                {
                    strWhere += "'";
                }
                strWhere += cond.ColR;
                if (string.IsNullOrEmpty(cond.TableR))
                {
                    strWhere += "'";
                }
            }
            if (orderArray != null)
            {
                string strOrderBy = "";
                foreach (SQLOrderBy _order in orderArray)
                {
                    if (tableMap.Keys.Contains(_order.tableName))
                    {
                        if (string.IsNullOrEmpty(strOrderBy))
                        {
                            strOrderBy += " ORDER BY ";
                        }
                        else
                        {
                            strOrderBy += ",";
                        }
                        strOrderBy += tableMap[_order.tableName];
                        strOrderBy += ".";
                        strOrderBy += _order.orderBy;
                    }
                }
                return "SELECT " + strSelect + " FROM " + strFrom + " WHERE " + strWhere + strOrderBy;
            }

            return "SELECT " + strSelect + " FROM " + strFrom + " WHERE " + strWhere;
        }

        public string ProductCount(string tableName, string colName)
        {
            if (colsArray == null) return "";
            if (colsArray.Count > chMap.Count()) return "";
            int pos = 0;
            string strFrom = "";
            string strWhere = "";
            string strSelect = "";
            Dictionary<string, string> tableMap = new Dictionary<string, string>();
            foreach (string st in colsArray.Keys)
            {
                tableMap.Add(st, chMap[pos].ToString());
                pos++;
            }
            foreach (string st in colsArray.Keys)
            {
                if (string.IsNullOrEmpty(st) == false)
                {
                    if (string.IsNullOrEmpty(strFrom) == false) strFrom += ",";
                    strFrom += st;
                    strFrom += " ";
                    strFrom += tableMap[st];
                }
                if (colsArray[st] == null) continue;
            }
            foreach (SQLCondition cond in condArray)
            {
                if (tableMap.Keys.Contains(cond.TableL) == false ||
                    tableMap.Keys.Contains(cond.TableR) == false)
                    continue;
                if (string.IsNullOrEmpty(strWhere) == false) strWhere += " AND ";
                if (string.IsNullOrEmpty(cond.TableL) == false)
                {
                    strWhere += tableMap[cond.TableL];
                    strWhere += ".";
                }
                else
                {
                    strWhere += "'";
                }
                strWhere += cond.ColL;
                if (string.IsNullOrEmpty(cond.TableL))
                {
                    strWhere += "'";
                }
                if (cond.condition == CONDITION.Less)
                {
                    strWhere += " < ";
                }
                else if (cond.condition == CONDITION.Equal)
                {
                    strWhere += " = ";
                }
                else if (cond.condition == CONDITION.Greater)
                {
                    strWhere += " > ";
                }
                if (string.IsNullOrEmpty(cond.TableR) == false)
                {
                    strWhere += tableMap[cond.TableR];
                    strWhere += ".";
                }
                else
                {
                    strWhere += "'";
                }
                strWhere += cond.ColR;
                if (string.IsNullOrEmpty(cond.TableR))
                {
                    strWhere += "'";
                }
            }
            strSelect = "COUNT(" + tableMap[tableName] + "." + colName + ")";
            if (orderArray != null)
            {
                string strOrderBy = "";
                foreach (SQLOrderBy _order in orderArray)
                {
                    if (tableMap.Keys.Contains(_order.tableName))
                    {
                        if (string.IsNullOrEmpty(strOrderBy))
                        {
                            strOrderBy += " ORDER BY ";
                        }
                        else
                        {
                            strOrderBy += ",";
                        }
                        strOrderBy += tableMap[_order.tableName];
                        strOrderBy += ".";
                        strOrderBy += _order.orderBy;
                    }
                }
                return "SELECT " + strSelect + " FROM " + strFrom + " WHERE " + strWhere + strOrderBy;
            }

            return "SELECT " + strSelect + " FROM " + strFrom + " WHERE " + strWhere;
        }
    }
}
